{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**数据探索**\n",
    "\n",
    "利用LightGBM/XGboost实现Happy Customer Bank目标客户（贷款成功的客户）识别\n",
    "\n",
    "\n",
    "    一、 任务说明：Happy Customer Bank目标客户识别\n",
    "https://discuss.analyticsvidhya.com/t/hackathon-3-x-predict-customer-worth-for-happy-customer-bank/3802\n",
    "\n",
    "1)\t文件说明\n",
    "Train.csv：训练数据\n",
    "Test.csv：测试数据\n",
    "\n",
    "2)\t字段说明\n",
    "数据集共26个字段: 其中1-24列为输入特征，25-26列为输出特征。\n",
    "    1. ID - 唯一ID（不能用于预测）\n",
    "    2. Gender - 性别\n",
    "    3. City - 城市\n",
    "    4. Monthly_Income - 月收入（以卢比为单位）\n",
    "    5. DOB - 出生日期\n",
    "    6. Lead_Creation_Date - 潜在（贷款）创建日期\n",
    "    7. Loan_Amount_Applied - 贷款申请请求金额（印度卢比，INR）\n",
    "    8. Loan_Tenure_Applied - 贷款申请期限（单位为年）\n",
    "    9. Existing_EMI -现有贷款的EMI（EMI：电子货币机构许可证） \n",
    "    10. Employer_Name雇主名称\n",
    "    11. Salary_Account - 薪资帐户银行\n",
    "    12. Mobile_Verified - 是否移动验证（Y / N）\n",
    "    13. VAR5 - 连续型变量\n",
    "    14. VAR1-  类别型变量\n",
    "    15. Loan_Amount_Submitted - 提交的贷款金额（在看到资格后修改和选择）\n",
    "    16. Loan_Tenure_Submitted - 提交的贷款期限（单位为年，在看到资格后修改和选择）\n",
    "    17. Interest_Rate - 提交贷款金额的利率\n",
    "    18. Processing_Fee - 提交贷款的处理费（INR）\n",
    "    19. EMI_Loan_Submitted -提交的EMI贷款金额（INR）\n",
    "    20. Filled_Form - 后期报价后是否已填写申请表格\n",
    "    21. Device_Type - 进行申请的设备（浏览器/移动设备）\n",
    "    22. Var2 - 类别型变量\n",
    "    23. Source - 类别型变量\n",
    "    24. Var4 - 类别型变量\n",
    "\n",
    "输出：\n",
    "    25. LoggedIn - 是否login（只用于理解问题的变量，不能用于预测，测试集中没有）\n",
    "    26. Disbursed - 是否发放贷款（目标变量），1为发放贷款（目标客户）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#首先 import 必要的模块\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/jhony/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py:2785: DtypeWarning: Columns (12,14,23) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>City</th>\n",
       "      <th>Monthly_Income</th>\n",
       "      <th>DOB</th>\n",
       "      <th>Lead_Creation_Date</th>\n",
       "      <th>Loan_Amount_Applied</th>\n",
       "      <th>Loan_Tenure_Applied</th>\n",
       "      <th>Existing_EMI</th>\n",
       "      <th>Employer_Name</th>\n",
       "      <th>...</th>\n",
       "      <th>Processing_Fee</th>\n",
       "      <th>EMI_Loan_Submitted</th>\n",
       "      <th>Filled_Form</th>\n",
       "      <th>Device_Type</th>\n",
       "      <th>Var2</th>\n",
       "      <th>Source</th>\n",
       "      <th>Var4</th>\n",
       "      <th>LoggedIn</th>\n",
       "      <th>Disbursed</th>\n",
       "      <th>Unnamed: 26</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ID000002C20</td>\n",
       "      <td>Female</td>\n",
       "      <td>Delhi</td>\n",
       "      <td>20000</td>\n",
       "      <td>23-May-78</td>\n",
       "      <td>15-May-15</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>CYBOSOL</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>G</td>\n",
       "      <td>S122</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ID000004E40</td>\n",
       "      <td>Male</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>35000</td>\n",
       "      <td>07-Oct-85</td>\n",
       "      <td>04-May-15</td>\n",
       "      <td>200000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TATA CONSULTANCY SERVICES LTD (TCS)</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6762.9</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>G</td>\n",
       "      <td>S122</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ID000007H20</td>\n",
       "      <td>Male</td>\n",
       "      <td>Panchkula</td>\n",
       "      <td>22500</td>\n",
       "      <td>10-Oct-81</td>\n",
       "      <td>19-May-15</td>\n",
       "      <td>600000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>ALCHEMIST HOSPITALS LTD</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S143</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ID000008I30</td>\n",
       "      <td>Male</td>\n",
       "      <td>Saharsa</td>\n",
       "      <td>35000</td>\n",
       "      <td>30-Nov-87</td>\n",
       "      <td>09-May-15</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>BIHAR GOVERNMENT</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S143</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ID000009J40</td>\n",
       "      <td>Male</td>\n",
       "      <td>Bengaluru</td>\n",
       "      <td>100000</td>\n",
       "      <td>17-Feb-84</td>\n",
       "      <td>20-May-15</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>25000.0</td>\n",
       "      <td>GLOBAL EDGE SOFTWARE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S134</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID  Gender       City  Monthly_Income        DOB  \\\n",
       "0  ID000002C20  Female      Delhi           20000  23-May-78   \n",
       "1  ID000004E40    Male     Mumbai           35000  07-Oct-85   \n",
       "2  ID000007H20    Male  Panchkula           22500  10-Oct-81   \n",
       "3  ID000008I30    Male    Saharsa           35000  30-Nov-87   \n",
       "4  ID000009J40    Male  Bengaluru          100000  17-Feb-84   \n",
       "\n",
       "  Lead_Creation_Date  Loan_Amount_Applied  Loan_Tenure_Applied  Existing_EMI  \\\n",
       "0          15-May-15             300000.0                  5.0           0.0   \n",
       "1          04-May-15             200000.0                  2.0           0.0   \n",
       "2          19-May-15             600000.0                  4.0           0.0   \n",
       "3          09-May-15            1000000.0                  5.0           0.0   \n",
       "4          20-May-15             500000.0                  2.0       25000.0   \n",
       "\n",
       "                         Employer_Name     ...     Processing_Fee  \\\n",
       "0                              CYBOSOL     ...                NaN   \n",
       "1  TATA CONSULTANCY SERVICES LTD (TCS)     ...                NaN   \n",
       "2              ALCHEMIST HOSPITALS LTD     ...                NaN   \n",
       "3                     BIHAR GOVERNMENT     ...                NaN   \n",
       "4                 GLOBAL EDGE SOFTWARE     ...                NaN   \n",
       "\n",
       "  EMI_Loan_Submitted Filled_Form  Device_Type Var2  Source  Var4  LoggedIn  \\\n",
       "0                NaN           N  Web-browser    G    S122     1         0   \n",
       "1             6762.9           N  Web-browser    G    S122     3         0   \n",
       "2                NaN           N  Web-browser    B    S143     1         0   \n",
       "3                NaN           N  Web-browser    B    S143     3         0   \n",
       "4                NaN           N  Web-browser    B    S134     3         1   \n",
       "\n",
       "   Disbursed Unnamed: 26  \n",
       "0          0         NaN  \n",
       "1          0         NaN  \n",
       "2          0         NaN  \n",
       "3          0         NaN  \n",
       "4          0         NaN  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#读取数据\n",
    "train = pd.read_csv(\"Train.csv\")\n",
    "test = pd.read_csv(\"Test.csv\")\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>City</th>\n",
       "      <th>Monthly_Income</th>\n",
       "      <th>DOB</th>\n",
       "      <th>Lead_Creation_Date</th>\n",
       "      <th>Loan_Amount_Applied</th>\n",
       "      <th>Loan_Tenure_Applied</th>\n",
       "      <th>Existing_EMI</th>\n",
       "      <th>Employer_Name</th>\n",
       "      <th>...</th>\n",
       "      <th>Loan_Tenure_Submitted</th>\n",
       "      <th>Interest_Rate</th>\n",
       "      <th>Processing_Fee</th>\n",
       "      <th>EMI_Loan_Submitted</th>\n",
       "      <th>Filled_Form</th>\n",
       "      <th>Device_Type</th>\n",
       "      <th>Var2</th>\n",
       "      <th>Source</th>\n",
       "      <th>Var4</th>\n",
       "      <th>Unnamed: 24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ID000026A10</td>\n",
       "      <td>Male</td>\n",
       "      <td>Dehradun</td>\n",
       "      <td>21500</td>\n",
       "      <td>03-Apr-87</td>\n",
       "      <td>05-May-15</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>APTARA INC</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>2649.39</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S122</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ID000054C40</td>\n",
       "      <td>Male</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>42000</td>\n",
       "      <td>12-May-80</td>\n",
       "      <td>01-May-15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>ATUL LTD</td>\n",
       "      <td>...</td>\n",
       "      <td>5.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>13800.0</td>\n",
       "      <td>19849.90</td>\n",
       "      <td>Y</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>C</td>\n",
       "      <td>S133</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ID000066O10</td>\n",
       "      <td>Female</td>\n",
       "      <td>Jaipur</td>\n",
       "      <td>10000</td>\n",
       "      <td>19-Sep-89</td>\n",
       "      <td>01-May-15</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SHAREKHAN PVT LTD</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S133</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ID000110G00</td>\n",
       "      <td>Female</td>\n",
       "      <td>Chennai</td>\n",
       "      <td>14650</td>\n",
       "      <td>15-Aug-91</td>\n",
       "      <td>01-May-15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>MAERSK GLOBAL SERVICE CENTRES</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>C</td>\n",
       "      <td>S133</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ID000113J30</td>\n",
       "      <td>Male</td>\n",
       "      <td>Chennai</td>\n",
       "      <td>23400</td>\n",
       "      <td>22-Jul-87</td>\n",
       "      <td>01-May-15</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>SCHAWK</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S143</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID  Gender      City  Monthly_Income        DOB  \\\n",
       "0  ID000026A10    Male  Dehradun           21500  03-Apr-87   \n",
       "1  ID000054C40    Male    Mumbai           42000  12-May-80   \n",
       "2  ID000066O10  Female    Jaipur           10000  19-Sep-89   \n",
       "3  ID000110G00  Female   Chennai           14650  15-Aug-91   \n",
       "4  ID000113J30    Male   Chennai           23400  22-Jul-87   \n",
       "\n",
       "  Lead_Creation_Date  Loan_Amount_Applied  Loan_Tenure_Applied  Existing_EMI  \\\n",
       "0          05-May-15             100000.0                  3.0           0.0   \n",
       "1          01-May-15                  0.0                  0.0           0.0   \n",
       "2          01-May-15             300000.0                  2.0           0.0   \n",
       "3          01-May-15                  0.0                  0.0           0.0   \n",
       "4          01-May-15             100000.0                  1.0        5000.0   \n",
       "\n",
       "                   Employer_Name     ...     Loan_Tenure_Submitted  \\\n",
       "0                     APTARA INC     ...                       3.0   \n",
       "1                       ATUL LTD     ...                       5.0   \n",
       "2              SHAREKHAN PVT LTD     ...                       NaN   \n",
       "3  MAERSK GLOBAL SERVICE CENTRES     ...                       NaN   \n",
       "4                         SCHAWK     ...                       2.0   \n",
       "\n",
       "  Interest_Rate Processing_Fee EMI_Loan_Submitted Filled_Form  Device_Type  \\\n",
       "0          20.0         1000.0            2649.39           N  Web-browser   \n",
       "1          24.0        13800.0           19849.90           Y       Mobile   \n",
       "2           NaN            NaN                NaN           N  Web-browser   \n",
       "3           NaN            NaN                NaN           N       Mobile   \n",
       "4           NaN            NaN                NaN           N  Web-browser   \n",
       "\n",
       "   Var2  Source  Var4 Unnamed: 24  \n",
       "0     B    S122     3         NaN  \n",
       "1     C    S133     5         NaN  \n",
       "2     B    S133     1         NaN  \n",
       "3     C    S133     1         NaN  \n",
       "4     B    S143     1         NaN  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train : (87020, 27)\n",
      "Test : (37717, 25)\n"
     ]
    }
   ],
   "source": [
    "print(\"Train :\", train.shape)\n",
    "print(\"Test :\", test.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 87020 entries, 0 to 87019\n",
      "Data columns (total 27 columns):\n",
      "ID                       87020 non-null object\n",
      "Gender                   87020 non-null object\n",
      "City                     86017 non-null object\n",
      "Monthly_Income           87020 non-null int64\n",
      "DOB                      87020 non-null object\n",
      "Lead_Creation_Date       87020 non-null object\n",
      "Loan_Amount_Applied      86949 non-null float64\n",
      "Loan_Tenure_Applied      86949 non-null float64\n",
      "Existing_EMI             86949 non-null float64\n",
      "Employer_Name            86949 non-null object\n",
      "Salary_Account           75257 non-null object\n",
      "Mobile_Verified          87019 non-null object\n",
      "Var5                     87020 non-null object\n",
      "Var1                     87020 non-null object\n",
      "Loan_Amount_Submitted    52409 non-null object\n",
      "Loan_Tenure_Submitted    52407 non-null float64\n",
      "Interest_Rate            27729 non-null float64\n",
      "Processing_Fee           27420 non-null float64\n",
      "EMI_Loan_Submitted       27726 non-null float64\n",
      "Filled_Form              87015 non-null object\n",
      "Device_Type              87020 non-null object\n",
      "Var2                     87020 non-null object\n",
      "Source                   87020 non-null object\n",
      "Var4                     87020 non-null object\n",
      "LoggedIn                 87020 non-null int64\n",
      "Disbursed                87020 non-null int64\n",
      "Unnamed: 26              12 non-null float64\n",
      "dtypes: float64(8), int64(3), object(16)\n",
      "memory usage: 17.9+ MB\n"
     ]
    }
   ],
   "source": [
    "train.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ID                           0\n",
      "Gender                       0\n",
      "City                      1003\n",
      "Monthly_Income               0\n",
      "DOB                          0\n",
      "Lead_Creation_Date           0\n",
      "Loan_Amount_Applied         71\n",
      "Loan_Tenure_Applied         71\n",
      "Existing_EMI                71\n",
      "Employer_Name               71\n",
      "Salary_Account           11763\n",
      "Mobile_Verified              1\n",
      "Var5                         0\n",
      "Var1                         0\n",
      "Loan_Amount_Submitted    34611\n",
      "Loan_Tenure_Submitted    34613\n",
      "Interest_Rate            59291\n",
      "Processing_Fee           59600\n",
      "EMI_Loan_Submitted       59294\n",
      "Filled_Form                  5\n",
      "Device_Type                  0\n",
      "Var2                         0\n",
      "Source                       0\n",
      "Var4                         0\n",
      "LoggedIn                     0\n",
      "Disbursed                    0\n",
      "Unnamed: 26              87008\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print (train.isnull().sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "该数据集存在缺失值，缺失值被标记为NaN。其中 Loan_Amount_Submitted 34611 Loan_Tenure_Submitted 34613 Interest_Rate 59291 Processing_Fee 59600 EMI_Loan_Submitted 59294 缺失值很多，接近总量的一半甚至更多。 Unnamed列无用，可以丢弃。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 37717 entries, 0 to 37716\n",
      "Data columns (total 25 columns):\n",
      "ID                       37717 non-null object\n",
      "Gender                   37717 non-null object\n",
      "City                     37319 non-null object\n",
      "Monthly_Income           37717 non-null int64\n",
      "DOB                      37717 non-null object\n",
      "Lead_Creation_Date       37717 non-null object\n",
      "Loan_Amount_Applied      37677 non-null float64\n",
      "Loan_Tenure_Applied      37677 non-null float64\n",
      "Existing_EMI             37677 non-null float64\n",
      "Employer_Name            37675 non-null object\n",
      "Salary_Account           32678 non-null object\n",
      "Mobile_Verified          37716 non-null object\n",
      "Var5                     37717 non-null object\n",
      "Var1                     37717 non-null object\n",
      "Loan_Amount_Submitted    22799 non-null object\n",
      "Loan_Tenure_Submitted    22795 non-null float64\n",
      "Interest_Rate            12112 non-null float64\n",
      "Processing_Fee           11971 non-null float64\n",
      "EMI_Loan_Submitted       12110 non-null float64\n",
      "Filled_Form              37711 non-null object\n",
      "Device_Type              37717 non-null object\n",
      "Var2                     37717 non-null object\n",
      "Source                   37717 non-null object\n",
      "Var4                     37717 non-null object\n",
      "Unnamed: 24              8 non-null float64\n",
      "dtypes: float64(8), int64(1), object(16)\n",
      "memory usage: 7.2+ MB\n"
     ]
    }
   ],
   "source": [
    "test.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ID                           0\n",
      "Gender                       0\n",
      "City                       398\n",
      "Monthly_Income               0\n",
      "DOB                          0\n",
      "Lead_Creation_Date           0\n",
      "Loan_Amount_Applied         40\n",
      "Loan_Tenure_Applied         40\n",
      "Existing_EMI                40\n",
      "Employer_Name               42\n",
      "Salary_Account            5039\n",
      "Mobile_Verified              1\n",
      "Var5                         0\n",
      "Var1                         0\n",
      "Loan_Amount_Submitted    14918\n",
      "Loan_Tenure_Submitted    14922\n",
      "Interest_Rate            25605\n",
      "Processing_Fee           25746\n",
      "EMI_Loan_Submitted       25607\n",
      "Filled_Form                  6\n",
      "Device_Type                  0\n",
      "Var2                         0\n",
      "Source                       0\n",
      "Var4                         0\n",
      "Unnamed: 24              37709\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print (test.isnull().sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "该数据集存在缺失值，缺失值被标记为NaN。其中\n",
    "Loan_Amount_Submitted    14918\n",
    "Loan_Tenure_Submitted    14922\n",
    "Interest_Rate            25607\n",
    "Processing_Fee           25746\n",
    "EMI_Loan_Submitted       25607\n",
    "缺失值很多，接近总量的一半甚至更多。\n",
    "Unnamed列无用，可以丢弃。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Monthly_Income</th>\n",
       "      <th>Loan_Amount_Applied</th>\n",
       "      <th>Loan_Tenure_Applied</th>\n",
       "      <th>Existing_EMI</th>\n",
       "      <th>Loan_Tenure_Submitted</th>\n",
       "      <th>Interest_Rate</th>\n",
       "      <th>Processing_Fee</th>\n",
       "      <th>EMI_Loan_Submitted</th>\n",
       "      <th>LoggedIn</th>\n",
       "      <th>Disbursed</th>\n",
       "      <th>Unnamed: 26</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>8.702000e+04</td>\n",
       "      <td>8.694900e+04</td>\n",
       "      <td>86949.000000</td>\n",
       "      <td>8.694900e+04</td>\n",
       "      <td>5.240700e+04</td>\n",
       "      <td>27729.000000</td>\n",
       "      <td>27420.000000</td>\n",
       "      <td>27726.000000</td>\n",
       "      <td>87020.000000</td>\n",
       "      <td>87020.000000</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>5.884997e+04</td>\n",
       "      <td>2.302507e+05</td>\n",
       "      <td>2.131399</td>\n",
       "      <td>3.696228e+03</td>\n",
       "      <td>9.738955e+01</td>\n",
       "      <td>19.191836</td>\n",
       "      <td>5129.135512</td>\n",
       "      <td>10997.698748</td>\n",
       "      <td>0.029867</td>\n",
       "      <td>0.014629</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.177511e+06</td>\n",
       "      <td>3.542068e+05</td>\n",
       "      <td>2.014193</td>\n",
       "      <td>3.981021e+04</td>\n",
       "      <td>8.725070e+03</td>\n",
       "      <td>5.840606</td>\n",
       "      <td>4724.051308</td>\n",
       "      <td>7512.114833</td>\n",
       "      <td>0.175342</td>\n",
       "      <td>0.120062</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>15.250000</td>\n",
       "      <td>1176.410000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.650000e+04</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>15.250000</td>\n",
       "      <td>2000.000000</td>\n",
       "      <td>6491.280000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2.500000e+04</td>\n",
       "      <td>1.000000e+05</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>4.000000e+00</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>4000.000000</td>\n",
       "      <td>9391.385000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>4.000000e+04</td>\n",
       "      <td>3.000000e+05</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>3.500000e+03</td>\n",
       "      <td>5.000000e+00</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>6250.000000</td>\n",
       "      <td>12919.040000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4.445544e+08</td>\n",
       "      <td>1.000000e+07</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>1.000000e+07</td>\n",
       "      <td>1.500000e+06</td>\n",
       "      <td>37.000000</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>144748.280000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Monthly_Income  Loan_Amount_Applied  Loan_Tenure_Applied  Existing_EMI  \\\n",
       "count    8.702000e+04         8.694900e+04         86949.000000  8.694900e+04   \n",
       "mean     5.884997e+04         2.302507e+05             2.131399  3.696228e+03   \n",
       "std      2.177511e+06         3.542068e+05             2.014193  3.981021e+04   \n",
       "min      0.000000e+00         0.000000e+00             0.000000  0.000000e+00   \n",
       "25%      1.650000e+04         0.000000e+00             0.000000  0.000000e+00   \n",
       "50%      2.500000e+04         1.000000e+05             2.000000  0.000000e+00   \n",
       "75%      4.000000e+04         3.000000e+05             4.000000  3.500000e+03   \n",
       "max      4.445544e+08         1.000000e+07            10.000000  1.000000e+07   \n",
       "\n",
       "       Loan_Tenure_Submitted  Interest_Rate  Processing_Fee  \\\n",
       "count           5.240700e+04   27729.000000    27420.000000   \n",
       "mean            9.738955e+01      19.191836     5129.135512   \n",
       "std             8.725070e+03       5.840606     4724.051308   \n",
       "min             1.000000e+00       1.000000       15.250000   \n",
       "25%             3.000000e+00      15.250000     2000.000000   \n",
       "50%             4.000000e+00      18.000000     4000.000000   \n",
       "75%             5.000000e+00      20.000000     6250.000000   \n",
       "max             1.500000e+06      37.000000    50000.000000   \n",
       "\n",
       "       EMI_Loan_Submitted      LoggedIn     Disbursed  Unnamed: 26  \n",
       "count        27726.000000  87020.000000  87020.000000         12.0  \n",
       "mean         10997.698748      0.029867      0.014629          0.0  \n",
       "std           7512.114833      0.175342      0.120062          0.0  \n",
       "min           1176.410000      0.000000      0.000000          0.0  \n",
       "25%           6491.280000      0.000000      0.000000          0.0  \n",
       "50%           9391.385000      0.000000      0.000000          0.0  \n",
       "75%          12919.040000      0.000000      0.000000          0.0  \n",
       "max         144748.280000      5.000000      1.000000          0.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查看数值型特征的基本统计量\n",
    "train.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Monthly_Income</th>\n",
       "      <th>Loan_Amount_Applied</th>\n",
       "      <th>Loan_Tenure_Applied</th>\n",
       "      <th>Existing_EMI</th>\n",
       "      <th>Loan_Tenure_Submitted</th>\n",
       "      <th>Interest_Rate</th>\n",
       "      <th>Processing_Fee</th>\n",
       "      <th>EMI_Loan_Submitted</th>\n",
       "      <th>Unnamed: 24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3.771700e+04</td>\n",
       "      <td>3.767700e+04</td>\n",
       "      <td>37677.000000</td>\n",
       "      <td>37677.000000</td>\n",
       "      <td>22795.000000</td>\n",
       "      <td>12112.00000</td>\n",
       "      <td>11971.000000</td>\n",
       "      <td>12110.000000</td>\n",
       "      <td>8.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>3.980311e+04</td>\n",
       "      <td>2.293886e+05</td>\n",
       "      <td>2.153887</td>\n",
       "      <td>3498.142270</td>\n",
       "      <td>45.580171</td>\n",
       "      <td>19.25701</td>\n",
       "      <td>5108.513783</td>\n",
       "      <td>10942.981538</td>\n",
       "      <td>2.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.361382e+05</td>\n",
       "      <td>3.539572e+05</td>\n",
       "      <td>2.019334</td>\n",
       "      <td>9857.470897</td>\n",
       "      <td>3306.815332</td>\n",
       "      <td>5.87996</td>\n",
       "      <td>4742.300034</td>\n",
       "      <td>7361.196742</td>\n",
       "      <td>1.246423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.00000</td>\n",
       "      <td>17.500000</td>\n",
       "      <td>1176.410000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.650000e+04</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>15.00000</td>\n",
       "      <td>2000.000000</td>\n",
       "      <td>6184.107500</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2.500000e+04</td>\n",
       "      <td>1.000000e+05</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>18.00000</td>\n",
       "      <td>3840.000000</td>\n",
       "      <td>9425.760000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>4.000000e+04</td>\n",
       "      <td>3.000000e+05</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>3500.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>20.00000</td>\n",
       "      <td>6250.000000</td>\n",
       "      <td>12840.030000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>3.500000e+07</td>\n",
       "      <td>1.500000e+07</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>430000.000000</td>\n",
       "      <td>320000.000000</td>\n",
       "      <td>37.00000</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>89552.030000</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Monthly_Income  Loan_Amount_Applied  Loan_Tenure_Applied  \\\n",
       "count    3.771700e+04         3.767700e+04         37677.000000   \n",
       "mean     3.980311e+04         2.293886e+05             2.153887   \n",
       "std      2.361382e+05         3.539572e+05             2.019334   \n",
       "min      0.000000e+00         0.000000e+00             0.000000   \n",
       "25%      1.650000e+04         0.000000e+00             0.000000   \n",
       "50%      2.500000e+04         1.000000e+05             2.000000   \n",
       "75%      4.000000e+04         3.000000e+05             4.000000   \n",
       "max      3.500000e+07         1.500000e+07            10.000000   \n",
       "\n",
       "        Existing_EMI  Loan_Tenure_Submitted  Interest_Rate  Processing_Fee  \\\n",
       "count   37677.000000           22795.000000    12112.00000    11971.000000   \n",
       "mean     3498.142270              45.580171       19.25701     5108.513783   \n",
       "std      9857.470897            3306.815332        5.87996     4742.300034   \n",
       "min         0.000000               1.000000        3.00000       17.500000   \n",
       "25%         0.000000               3.000000       15.00000     2000.000000   \n",
       "50%         0.000000               4.000000       18.00000     3840.000000   \n",
       "75%      3500.000000               5.000000       20.00000     6250.000000   \n",
       "max    430000.000000          320000.000000       37.00000    50000.000000   \n",
       "\n",
       "       EMI_Loan_Submitted  Unnamed: 24  \n",
       "count        12110.000000     8.000000  \n",
       "mean         10942.981538     2.125000  \n",
       "std           7361.196742     1.246423  \n",
       "min           1176.410000     1.000000  \n",
       "25%           6184.107500     1.000000  \n",
       "50%           9425.760000     2.000000  \n",
       "75%          12840.030000     3.000000  \n",
       "max          89552.030000     4.000000  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        40\n",
       "1        33\n",
       "2        37\n",
       "3        31\n",
       "4        34\n",
       "5        36\n",
       "6        31\n",
       "7        43\n",
       "8        46\n",
       "9        29\n",
       "10       42\n",
       "11       29\n",
       "12       28\n",
       "13       36\n",
       "14       29\n",
       "15       36\n",
       "16       33\n",
       "17       28\n",
       "18       42\n",
       "19       36\n",
       "20       33\n",
       "21       35\n",
       "22       31\n",
       "23       45\n",
       "24       43\n",
       "25       37\n",
       "26       27\n",
       "27       36\n",
       "28       24\n",
       "29       33\n",
       "         ..\n",
       "86990    45\n",
       "86991    45\n",
       "86992    26\n",
       "86993    37\n",
       "86994    34\n",
       "86995    40\n",
       "86996    32\n",
       "86997    32\n",
       "86998   -38\n",
       "86999    28\n",
       "87000   -44\n",
       "87001    31\n",
       "87002    28\n",
       "87003   -42\n",
       "87004    40\n",
       "87005    49\n",
       "87006    29\n",
       "87007    30\n",
       "87008    37\n",
       "87009    33\n",
       "87010    45\n",
       "87011    28\n",
       "87012    33\n",
       "87013    28\n",
       "87014    36\n",
       "87015    49\n",
       "87016    28\n",
       "87017    46\n",
       "87018    41\n",
       "87019    30\n",
       "Name: Age, Length: 87020, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#求出train&test数据中客户的年龄\n",
    "import datetime as dt\n",
    "train['DOB']=pd.to_datetime(train['DOB'])\n",
    "test['DOB']=pd.to_datetime(test['DOB'])\n",
    "now_year=dt.datetime.today().year#当前年份\n",
    "train['Age']=now_year-train.DOB.dt.year\n",
    "test['Age']=now_year-test.DOB.dt.year\n",
    "train['Age']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        31\n",
       "1        38\n",
       "2        29\n",
       "3        27\n",
       "4        31\n",
       "5        32\n",
       "6        46\n",
       "7        28\n",
       "8        28\n",
       "9        35\n",
       "10       29\n",
       "11       33\n",
       "12       23\n",
       "13       29\n",
       "14       31\n",
       "15       32\n",
       "16       39\n",
       "17       30\n",
       "18       46\n",
       "19       31\n",
       "20       27\n",
       "21       43\n",
       "22       34\n",
       "23       34\n",
       "24       35\n",
       "25       31\n",
       "26       30\n",
       "27       33\n",
       "28       27\n",
       "29       39\n",
       "         ..\n",
       "37687    30\n",
       "37688   -43\n",
       "37689    28\n",
       "37690    25\n",
       "37691    27\n",
       "37692    46\n",
       "37693    27\n",
       "37694    30\n",
       "37695    27\n",
       "37696    27\n",
       "37697    27\n",
       "37698    29\n",
       "37699    26\n",
       "37700    26\n",
       "37701    29\n",
       "37702   -42\n",
       "37703    25\n",
       "37704    27\n",
       "37705    24\n",
       "37706    43\n",
       "37707    39\n",
       "37708    33\n",
       "37709    32\n",
       "37710    26\n",
       "37711    29\n",
       "37712    47\n",
       "37713    34\n",
       "37714    49\n",
       "37715    46\n",
       "37716    30\n",
       "Name: Age, Length: 37717, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test['Age']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>City</th>\n",
       "      <th>Monthly_Income</th>\n",
       "      <th>DOB</th>\n",
       "      <th>Lead_Creation_Date</th>\n",
       "      <th>Loan_Amount_Applied</th>\n",
       "      <th>Loan_Tenure_Applied</th>\n",
       "      <th>Existing_EMI</th>\n",
       "      <th>Employer_Name</th>\n",
       "      <th>...</th>\n",
       "      <th>EMI_Loan_Submitted</th>\n",
       "      <th>Filled_Form</th>\n",
       "      <th>Device_Type</th>\n",
       "      <th>Var2</th>\n",
       "      <th>Source</th>\n",
       "      <th>Var4</th>\n",
       "      <th>LoggedIn</th>\n",
       "      <th>Disbursed</th>\n",
       "      <th>Unnamed: 26</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ID000002C20</td>\n",
       "      <td>Female</td>\n",
       "      <td>Delhi</td>\n",
       "      <td>20000</td>\n",
       "      <td>1978-05-23</td>\n",
       "      <td>15-May-15</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>CYBOSOL</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>G</td>\n",
       "      <td>S122</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ID000004E40</td>\n",
       "      <td>Male</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>35000</td>\n",
       "      <td>1985-10-07</td>\n",
       "      <td>04-May-15</td>\n",
       "      <td>200000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TATA CONSULTANCY SERVICES LTD (TCS)</td>\n",
       "      <td>...</td>\n",
       "      <td>6762.9</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>G</td>\n",
       "      <td>S122</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ID000007H20</td>\n",
       "      <td>Male</td>\n",
       "      <td>Panchkula</td>\n",
       "      <td>22500</td>\n",
       "      <td>1981-10-10</td>\n",
       "      <td>19-May-15</td>\n",
       "      <td>600000.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>ALCHEMIST HOSPITALS LTD</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S143</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ID000008I30</td>\n",
       "      <td>Male</td>\n",
       "      <td>Saharsa</td>\n",
       "      <td>35000</td>\n",
       "      <td>1987-11-30</td>\n",
       "      <td>09-May-15</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>BIHAR GOVERNMENT</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S143</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ID000009J40</td>\n",
       "      <td>Male</td>\n",
       "      <td>Bengaluru</td>\n",
       "      <td>100000</td>\n",
       "      <td>1984-02-17</td>\n",
       "      <td>20-May-15</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>25000.0</td>\n",
       "      <td>GLOBAL EDGE SOFTWARE</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S134</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID  Gender       City  Monthly_Income        DOB  \\\n",
       "0  ID000002C20  Female      Delhi           20000 1978-05-23   \n",
       "1  ID000004E40    Male     Mumbai           35000 1985-10-07   \n",
       "2  ID000007H20    Male  Panchkula           22500 1981-10-10   \n",
       "3  ID000008I30    Male    Saharsa           35000 1987-11-30   \n",
       "4  ID000009J40    Male  Bengaluru          100000 1984-02-17   \n",
       "\n",
       "  Lead_Creation_Date  Loan_Amount_Applied  Loan_Tenure_Applied  Existing_EMI  \\\n",
       "0          15-May-15             300000.0                  5.0           0.0   \n",
       "1          04-May-15             200000.0                  2.0           0.0   \n",
       "2          19-May-15             600000.0                  4.0           0.0   \n",
       "3          09-May-15            1000000.0                  5.0           0.0   \n",
       "4          20-May-15             500000.0                  2.0       25000.0   \n",
       "\n",
       "                         Employer_Name ... EMI_Loan_Submitted Filled_Form  \\\n",
       "0                              CYBOSOL ...                NaN           N   \n",
       "1  TATA CONSULTANCY SERVICES LTD (TCS) ...             6762.9           N   \n",
       "2              ALCHEMIST HOSPITALS LTD ...                NaN           N   \n",
       "3                     BIHAR GOVERNMENT ...                NaN           N   \n",
       "4                 GLOBAL EDGE SOFTWARE ...                NaN           N   \n",
       "\n",
       "   Device_Type Var2 Source  Var4  LoggedIn  Disbursed  Unnamed: 26 Age  \n",
       "0  Web-browser    G   S122     1         0          0          NaN  40  \n",
       "1  Web-browser    G   S122     3         0          0          NaN  33  \n",
       "2  Web-browser    B   S143     1         0          0          NaN  37  \n",
       "3  Web-browser    B   S143     3         0          0          NaN  31  \n",
       "4  Web-browser    B   S134     3         1          0          NaN  34  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Gender</th>\n",
       "      <th>City</th>\n",
       "      <th>Monthly_Income</th>\n",
       "      <th>DOB</th>\n",
       "      <th>Lead_Creation_Date</th>\n",
       "      <th>Loan_Amount_Applied</th>\n",
       "      <th>Loan_Tenure_Applied</th>\n",
       "      <th>Existing_EMI</th>\n",
       "      <th>Employer_Name</th>\n",
       "      <th>...</th>\n",
       "      <th>Interest_Rate</th>\n",
       "      <th>Processing_Fee</th>\n",
       "      <th>EMI_Loan_Submitted</th>\n",
       "      <th>Filled_Form</th>\n",
       "      <th>Device_Type</th>\n",
       "      <th>Var2</th>\n",
       "      <th>Source</th>\n",
       "      <th>Var4</th>\n",
       "      <th>Unnamed: 24</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ID000026A10</td>\n",
       "      <td>Male</td>\n",
       "      <td>Dehradun</td>\n",
       "      <td>21500</td>\n",
       "      <td>1987-04-03</td>\n",
       "      <td>05-May-15</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>APTARA INC</td>\n",
       "      <td>...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>2649.39</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S122</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ID000054C40</td>\n",
       "      <td>Male</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>42000</td>\n",
       "      <td>1980-05-12</td>\n",
       "      <td>01-May-15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>ATUL LTD</td>\n",
       "      <td>...</td>\n",
       "      <td>24.0</td>\n",
       "      <td>13800.0</td>\n",
       "      <td>19849.90</td>\n",
       "      <td>Y</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>C</td>\n",
       "      <td>S133</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ID000066O10</td>\n",
       "      <td>Female</td>\n",
       "      <td>Jaipur</td>\n",
       "      <td>10000</td>\n",
       "      <td>1989-09-19</td>\n",
       "      <td>01-May-15</td>\n",
       "      <td>300000.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>SHAREKHAN PVT LTD</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S133</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ID000110G00</td>\n",
       "      <td>Female</td>\n",
       "      <td>Chennai</td>\n",
       "      <td>14650</td>\n",
       "      <td>1991-08-15</td>\n",
       "      <td>01-May-15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>MAERSK GLOBAL SERVICE CENTRES</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Mobile</td>\n",
       "      <td>C</td>\n",
       "      <td>S133</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ID000113J30</td>\n",
       "      <td>Male</td>\n",
       "      <td>Chennai</td>\n",
       "      <td>23400</td>\n",
       "      <td>1987-07-22</td>\n",
       "      <td>01-May-15</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>SCHAWK</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>N</td>\n",
       "      <td>Web-browser</td>\n",
       "      <td>B</td>\n",
       "      <td>S143</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ID  Gender      City  Monthly_Income        DOB  \\\n",
       "0  ID000026A10    Male  Dehradun           21500 1987-04-03   \n",
       "1  ID000054C40    Male    Mumbai           42000 1980-05-12   \n",
       "2  ID000066O10  Female    Jaipur           10000 1989-09-19   \n",
       "3  ID000110G00  Female   Chennai           14650 1991-08-15   \n",
       "4  ID000113J30    Male   Chennai           23400 1987-07-22   \n",
       "\n",
       "  Lead_Creation_Date  Loan_Amount_Applied  Loan_Tenure_Applied  Existing_EMI  \\\n",
       "0          05-May-15             100000.0                  3.0           0.0   \n",
       "1          01-May-15                  0.0                  0.0           0.0   \n",
       "2          01-May-15             300000.0                  2.0           0.0   \n",
       "3          01-May-15                  0.0                  0.0           0.0   \n",
       "4          01-May-15             100000.0                  1.0        5000.0   \n",
       "\n",
       "                   Employer_Name ... Interest_Rate Processing_Fee  \\\n",
       "0                     APTARA INC ...          20.0         1000.0   \n",
       "1                       ATUL LTD ...          24.0        13800.0   \n",
       "2              SHAREKHAN PVT LTD ...           NaN            NaN   \n",
       "3  MAERSK GLOBAL SERVICE CENTRES ...           NaN            NaN   \n",
       "4                         SCHAWK ...           NaN            NaN   \n",
       "\n",
       "  EMI_Loan_Submitted Filled_Form  Device_Type  Var2  Source  Var4  \\\n",
       "0            2649.39           N  Web-browser     B    S122     3   \n",
       "1           19849.90           Y       Mobile     C    S133     5   \n",
       "2                NaN           N  Web-browser     B    S133     1   \n",
       "3                NaN           N       Mobile     C    S133     1   \n",
       "4                NaN           N  Web-browser     B    S143     1   \n",
       "\n",
       "   Unnamed: 24 Age  \n",
       "0          NaN  31  \n",
       "1          NaN  38  \n",
       "2          NaN  29  \n",
       "3          NaN  27  \n",
       "4          NaN  31  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import LabelEncoder\n",
    "labelencoder=LabelEncoder()\n",
    "\n",
    "columns=['Gender','Lead_Creation_Date','Var1','Device_Type','Var2','Source']\n",
    "\n",
    "#'City','Var5','Loan_Amount_Submitted','Employer_Name','Mobile_Verified','Salary_Account','Filled_Form','Var4'\n",
    "for col in train[columns]:\n",
    "    train[col] = labelencoder.fit_transform(train[col])\n",
    "    test[col] = labelencoder.fit_transform(test[col])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import LabelEncoder\n",
    "labelencoder=LabelEncoder()\n",
    "\n",
    "columns=['City','Var5','Loan_Amount_Submitted','Employer_Name','Mobile_Verified','Salary_Account','Filled_Form','Var4']\n",
    "\n",
    "for col in train[columns]:\n",
    "    train[col] = train[col].astype(str)\n",
    "    test[col] = test[col].astype(str)\n",
    "    train[col] = labelencoder.fit_transform(train[col])\n",
    "    test[col] = labelencoder.fit_transform(test[col])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "categorical_features = ['City','Var5','Loan_Amount_Submitted','Employer_Name',\n",
    "                        'Mobile_Verified','Salary_Account','Filled_Form','Var4']\n",
    "#'City','Var5','Loan_Amount_Submitted','Employer_Name','Mobile_Verified','Salary_Account','Filled_Form','Var4'\n",
    "#'Gender','City','DOB','Lead_Creation_Date','Employer_Name','Salary_Account','Mobile_Verified','Var5',\n",
    "        #'Var1','Loan_Amount_Submitted','Filled_Form','Device_Type','Var2','Source','Var4'\n",
    "for col in train[categorical_features]:\n",
    "    train[col] = pd.get_dummies(train[col])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "from sklearn.preprocessing import OneHotEncoder\n",
    "\n",
    "one = OneHotEncoder()\n",
    "\n",
    "columns=['Gender','City','DOB','Lead_Creation_Date','Employer_Name','Salary_Account','Mobile_Verified','Var5',\n",
    "        'Var1','Loan_Amount_Submitted','Filled_Form','Device_Type','Var2','Source','Var4']\n",
    "for col in train[columns]:\n",
    "    train[col] = OneHotEncoder.fit_transform(train[col])\n",
    "for col in test[columns]:\n",
    "    test[col] = OneHotEncoder.transform(test[col])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**xgboost可以不做标准化处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#from sklearn.preprocessing import StandardScaler\n",
    "\n",
    "#ss_X = StandardScaler()\n",
    "\n",
    "\n",
    "X_train = train.drop(['ID','DOB','LoggedIn'], axis=1)\n",
    "X_test = test.drop(['ID','DOB'], axis=1)\n",
    "y_train = train['Disbursed']\n",
    "\n",
    "\n",
    "#X_train = ss_X.fit_transform(X_train)\n",
    "#X_test = ss_X.transform(X_test)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**特征处理结果存为文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "train.to_csv('FE_train.csv',index = False)\n",
    "test.to_csv('FE_test.csv',index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
